package edu.cibertec.dao;

import edu.cibertec.bean.*;
import edu.cibertec.connection.*;

import java.sql.*;
import java.util.ArrayList;


import edu.cibertec.bean.BeanCombo;
import edu.cibertec.connection.DBConnection;


public class SqlComboDAO implements ComboDAO  {

  // Para obtener una conexion.
	private 	Connection getConnection(){
		DBConnection conex=new DBConnection();
		return conex.getConnection(DBConnection.MYSQL);
	}

public ArrayList<BeanCombo> obtenerAreas() throws Exception {
	   ArrayList<BeanCombo> listado = new ArrayList<BeanCombo>();

	    Connection cn = this.getConnection();

	    PreparedStatement st = cn.prepareStatement("select * from tb_area");

	    ResultSet rs = st.executeQuery();

	    while (rs.next()) {
	      BeanCombo elemento = new BeanCombo();
	      elemento.setCodigo(rs.getString(1));
	      elemento.setDescripcion(rs.getString(2));

	      listado.add(elemento);
	    }

	    rs.close();
	    st.close();
	    cn.close();

	    return listado;
}

public ArrayList<BeanCombo> obtenerDepartamentoArea(String varea ) throws Exception {

    ArrayList<BeanCombo> listado = new ArrayList<BeanCombo>();

    Connection cn = this.getConnection();
    //select a.cod_area, a.cod_dpto, d.desc_dpto from tb_area a inner join tb_dpto d on a.cod_dpto = d.cod_dpto where a.cod_dpto =  ?

    PreparedStatement st = cn.prepareStatement(
        "select * from tb_dpto where cod_area = ?");
      st.setString(1, varea);
    
    ResultSet rs = st.executeQuery();

    while (rs.next()) {
      BeanCombo elemento = new BeanCombo();
      elemento.setCodigo(rs.getString(1));
      elemento.setDescripcion(rs.getString(2));

      listado.add(elemento);
      System.out.println(rs.getString(1) +	" " + rs.getString(2));
    }

    rs.close();
    st.close();
    cn.close();

    return listado;
  }


public ArrayList<BeanCombo> obtenerUsuarios() throws Exception {
	 ArrayList<BeanCombo> listado = new ArrayList<BeanCombo>();

	    Connection cn = this.getConnection();

	    PreparedStatement st = cn.prepareStatement("select * from tb_usuario where cod_tip_usu = 'TIPO02'");

	    ResultSet rs = st.executeQuery();

	    while (rs.next()) {
	      BeanCombo elemento = new BeanCombo();
	      elemento.setCodigo(rs.getString(1));
	      elemento.setDescripcion(rs.getString(2));

	      listado.add(elemento);
	      System.out.println(rs.getString(1) +	" " + rs.getString(2));
	    }

	    rs.close();
	    st.close();
	    cn.close();

	    return listado;
}





}



